"""
Case Type   : mysql protocol compatibility
Case Name   : 协议兼容下验证验证自定义函数
Create At   : 2024/10/23
Owner       : lvlintao666
Description :
    1、设置协议兼容相关配置
    2、建表插入数据
    3、创建函数
    4、调用函数
    5、查询函数
    6、清理环境
Expect      :
    1、配置成功
    2、建表插入数据成功
    3、创建函数成功
    4、调用函数成功
    5、查询函数成功
    6、清理成功
History     :
"""

import os
import time
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonMySQL import CommonMySQL
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class ProtoTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com = Common()
        self.comsh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.my_node = Node('mysql5')
        self.db_name = "proto_test_db"
        self.conf_path = os.path.join(macro.DB_INSTANCE_PATH,
                                      'postgresql.conf')
        self.create_db = f"drop database if exists {self.db_name};" \
                         f"create database {self.db_name} dbcompatibility='B'"
        self.user = f"proto_test_30"
        self.password = f"proto_test123"
        self.create_u = f"drop user if exists {self.user} cascade;" \
                        f"create user {self.user} with password '{self.password}';" \
                        f"select set_native_password('{self.user}','{self.password}','');"
        self.show_cmd = f"show enable_dolphin_proto;" \
                        f"show dolphin_server_port;"
        self.c_tb = f"drop table if exists tab_protocol_0030;" \
                    f"create table tab_protocol_0030(cid int, account decimal(7,3));" \
                    f"insert into tab_protocol_0030 values(001, 666.66);"
        self.function = f"drop function if exists func_protocol_0030;\n" \
                        f"DELIMITER // \n" \
                        f"create function func_protocol_0030(card_id int) " \
                        f"returns char(50) contains sql \n" \
                        f"begin \n" \
                        f"declare a char(50); \n" \
                        f"declare money decimal(7,3);  \n" \
                        f"declare num int; \n" \
                        f"select account into money from tab_protocol_0030 where cid=card_id;\n" \
                        f"set num = truncate(money/100,0); \n" \
                        f"case num \n" \
                        f"when 0 then set a='123'; \n" \
                        f"when 1 then set a='456'; \n" \
                        f"else set a='789';\n" \
                        f"end case; \n" \
                        f"return a; \n" \
                        f"end //\n" \
                        f"DELIMITER ;"
        self.call = f"select func_protocol_0030(1);"
        self.query = f"show function status where Name like 'func_protocol_0030';"
        self.clean_tb = f"drop function if exists func_protocol_0030;" \
                        f"drop table if exists tab_protocol_0030 cascade constraints;" \
                        f"drop user if exists {self.user} cascade;"
        self.clean_db = f"drop database {self.db_name};"

    def test_index(self):
        text = '--step1:设置协议兼容相关配置;expect:创建成功--'
        logger.info(text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = on')
        logger.info(msg)
        my_port = int(self.pri_node.db_port) + 10
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = {my_port}')
        logger.info(msg)
        sed_cmd = f"sed -i '$a dolphin.default_database_name={self.db_name}' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)

        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        show_res = self.comsh.execut_db_sql(self.show_cmd)
        logger.info(show_res)
        self.assertIn('on', show_res, '执行失败' + text)
        self.assertIn(f'{my_port}', show_res, '执行失败' + text)

        logger.info(self.create_db)
        db_res = self.comsh.execut_db_sql(self.create_db)
        logger.info(db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_res,
                      '执行失败' + text)
        logger.info(self.create_u)
        u_res = self.comsh.execut_db_sql(self.create_u, dbname=self.db_name)
        logger.info(u_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, u_res,
                      '执行失败' + text)

        text = '--step2:M*连接数据库建表插入数据;expect:创建成功--'
        logger.info(text)
        my_sh = CommonMySQL('mysql5')
        c_res = my_sh.execut_db_sql(sql=self.c_tb, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(c_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)

        text = '--step3:M*连接数据库create function begin end场景case控制语句;expect:创建成功--'
        logger.info(text)
        pro_res = my_sh.execut_db_sql(sql=self.function, dbname=self.db_name, host=self.pri_node.db_host,
                                      port=my_port, user=self.user, password=self.password)
        logger.info(pro_res)
        self.assertNotIn('ERROR', pro_res, '执行失败' + text)

        text = '--step4:M*连接数据库调用函数;expect:创建成功--'
        logger.info(text)
        pro_res = my_sh.execut_db_sql(sql=self.call, dbname=self.db_name, host=self.pri_node.db_host,
                                      port=my_port, user=self.user, password=self.password)
        logger.info(pro_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)

        text = '--step5:M*连接数据库show语法查询函数;expect:创建成功--'
        logger.info(text)
        q_res = my_sh.execut_db_sql(sql=self.query, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(q_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)

    def tearDown(self):
        text = '--step4:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        clean_res = self.comsh.execut_db_sql(self.clean_db)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, clean_res,
                      '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = off')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = 3308')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        sed_cmd = f"sed -i '/dolphin.default_database_name={self.db_name}/d' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        status = self.comsh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
